package com.teamsun.rptsql;

import com.teamsun.rptcfg.BaseRpt;
import com.teamsun.rptcfg.SelectDim;
import com.teamsun.rptcfg.TreeDim;
import com.teamsun.rptsql.mysql.CreateMySqlSQL;
import com.teamsun.rptsql.mysql.MysqlFunction;
import com.teamsun.rptsql.oracle.CreateOracleSQL;
import com.teamsun.rptsql.oracle.OracleFunction;
import com.teamsun.util.Constants;
import org.apache.commons.lang3.StringUtils;

import java.util.Map;

/**
 * <p>动态生成SQL统一入口</p>
 * <p>根据不同的数据库数据库类型动态生成SQL</p>
 */
public class DynamicSQL {

    private ICreateSQL createSQL;

    private IDBFunction dbFunction;

    public DynamicSQL() {

    }

    /**
     * 构造函数(根据不同的数据库类型生成不同的拼接SQL实现类)
     * @param dbType
     */
    public DynamicSQL(String dbType) {
        if(Constants.DBTYPE_ORACLE.equalsIgnoreCase(dbType)) {
            // oracle 数据库
            this.dbFunction = new OracleFunction();
            this.createSQL = new CreateOracleSQL(dbFunction);
        } else if(Constants.DBTYPE_MYSQL.equalsIgnoreCase(dbType)) {
            // mysql数据库
            this.dbFunction = new MysqlFunction();
            this.createSQL = new CreateMySqlSQL(dbFunction);
        }
    }

    /**
     * 拼接查询明细数据SQL
     * @param baseRpt  报表配置信息
     * @param params 查询条件
     * @return
     */
    public String getRptSql(BaseRpt baseRpt, Map<String, String> params) {
        // 最终生成的查询SQL
        String finalSQL = createSQL.getRptSql(baseRpt, params);

        return finalSQL;
    }

    /**
     * 拼接汇总SQL
     * @param baseRpt  报表配置信息
     * @param params 查询条件
     * @param rptSQL 明细查询SQL
     * @return
     */
    public String getSumRptSql(BaseRpt baseRpt, Map<String, String> params, String rptSQL) {
        String finalSQL = createSQL.getSumRptSql(baseRpt, params, rptSQL);

        return finalSQL;
    }

    /**
     * 拼接分页查询明细数据SQL
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param startIndex 起始行
     * @param endIndex 结束行
     * @return
     */
    public String getRptSqlByPage(BaseRpt baseRpt, Map<String, String> params, int startIndex, int endIndex) {

        // 最终生成的查询SQL
        String finalSQL = createSQL.getRptSqlByPage(baseRpt, params, startIndex, endIndex);

        return finalSQL;
    }

    /**
     * 拼接分页汇总SQL
     * @param baseRpt 报表配置信息
     * @param params 查询条件
     * @param rptSqlByPage 明细查询SQL
     * @return
     */
    public String getSumRptSqlByPage(BaseRpt baseRpt, Map<String, String> params, String rptSqlByPage) {
        String finalSQL = createSQL.getSumRptSqlByPage(baseRpt, params, rptSqlByPage);

        return finalSQL;
    }

    /**
     * 拼接查询数据量SQL
     * @param baseRpt  报表配置信息
     * @param params 查询条件
     * @return
     */
    public String getRptCountSql(BaseRpt baseRpt, Map<String, String> params) {
        String finalSQL = createSQL.getRptCountSql(baseRpt, params);

        return finalSQL;
    }

    /**
     * 拼接查询下拉框数据SQL
     * @param selectDim 下拉框数据维度表信息
     * @return
     */
    public String getSelectSql(SelectDim selectDim) {
        String tableName = selectDim.getTable();  // 表名
        String code = selectDim.getCode();  // 编码字段
        String name = selectDim.getName();  // 名称字段
        String order = selectDim.getOrder();  // 排序字段及排序方式
        String condition = selectDim.getCondition();  // 顾虑条件

        // 拼接查询SQL
        String selectSQL = "SELECT " + code + " AS \"key\"," + name + " AS \"value\" FROM " + tableName;

        // 如果配置过滤条件，则拼接过滤条件
        if(StringUtils.isNotBlank(condition)) {
            selectSQL = selectSQL + " WHERE " + condition;
        }
        // 如果配置排序字段，则拼接排序字段
        if(StringUtils.isNotBlank(order)) {
            selectSQL = selectSQL + " ORDER BY " + order;
        }

        return selectSQL;
    }

    /**
     * 拼接查询树数据SQL
     * @param treeDim 树数据维度表
     * @param param 查询条件(如需要权限控制时,需根据传入的参数查询)
     * @return
     */
    public String getTreeSql(TreeDim treeDim, String param, String parentParam, String parentId) {
        String tableName = treeDim.getTable();  // 表名
        String code = treeDim.getCode();  // 编码字段
        String name = treeDim.getName();  // 名称字段
        String upCode = treeDim.getUpcode();  // 上级编码字段
        String order = treeDim.getOrder();  // 排序字段及排序方式
        String rightField = treeDim.getRightField();  // 权限控制字段
        String parentRightField = treeDim.getParentRightField();  // 权限控制字段

        int paramLen = 0;
        boolean rowFlag = false;
        // 拼接查询查询SQL
        String treeSQL = "SELECT " + code + " AS \"code\"," + name + " AS \"name\"," + upCode + " AS \"upcode\" FROM " + tableName;
        if(StringUtils.isNotBlank(parentId)) {
            treeSQL = treeSQL + " WHERE " + upCode + " = \'" + parentId + "\'";
        } else if(StringUtils.isNotBlank(param)) {
            paramLen = param.split(",").length;
            rowFlag = true;
            if(param.contains(",")) {
                String queryParam = "";
                String[] params = param.split(",");
                for(int i=0;i<params.length; i++) {
                    queryParam = queryParam + "\'" + params[i] + "\',";
                }
                if(queryParam.endsWith(",")) {
                    queryParam = queryParam.substring(0, queryParam.length() - 1);
                }
                if(StringUtils.isNotBlank(rightField)) {
                    treeSQL = treeSQL + " WHERE " + rightField + " IN (" + queryParam + ")";
                } else {
                    treeSQL = treeSQL + " WHERE " + code + " IN (" + queryParam + ")";
                }
            } else {
                if(StringUtils.isNotBlank(rightField)) {
                    treeSQL = treeSQL + " WHERE " + rightField + " IN (\'" + param + "\')";
                } else {
                    treeSQL = treeSQL + " WHERE " + code + " IN (\'" + param + "\')";
                }
            }
        } else if(StringUtils.isNotBlank(parentParam)) {
            if(StringUtils.isNotBlank(parentRightField)) {
                treeSQL = treeSQL + " WHERE " + parentRightField + " IN (\'" + parentParam + "\')";
            } else {
                treeSQL = treeSQL + " WHERE " + upCode + " IN (\'" + parentParam + "\')";
            }
        } else {
            String rootCode = treeDim.getRootCode();
            if(StringUtils.isNotBlank(rootCode)) {
                //treeSQL = treeSQL + " WHERE " + upCode + " = \'" +  rootCode + "\'";
                rowFlag = true;
                paramLen = 1;
                treeSQL = treeSQL + " WHERE " + code + " = \'" +  rootCode + "\'";
            } else {
                treeSQL = treeSQL + " WHERE " + upCode + " IS NULL";
            }
        }

        // 如果配置排序字段，则拼接排序字段
        if(StringUtils.isNotBlank(order)) {
            treeSQL = treeSQL + " ORDER BY " + order;
        }

        if(rowFlag) {
            treeSQL = "SELECT * FROM ( " + treeSQL + " ) WHERE ROWNUM <= " + paramLen;
        }

        return treeSQL;
    }

    /**
     * 拼接查询树数据SQL
     * @param treeDim 树数据维度表
     * @param param 查询条件(如需要权限控制时,需根据传入的参数查询)
     * @return
     */
    public String getTreeSqlBySearch(TreeDim treeDim, String param, String parentParam, String parentId, String searchName) {
        String tableName = treeDim.getTable();  // 表名
        String code = treeDim.getCode();  // 编码字段
        String name = treeDim.getName();  // 名称字段
        String upCode = treeDim.getUpcode();  // 上级编码字段
        String order = treeDim.getOrder();  // 排序字段及排序方式

        int paramLen = 0;
        boolean rowFlag = false;
        // 拼接查询查询SQL
        String treeSQL = "SELECT " + code + " AS \"code\"," + name + " AS \"name\"," + upCode + " AS \"upcode\" FROM " + tableName + " WHERE 1 = 1";
        if(StringUtils.isNotBlank(parentId)) {
            treeSQL = treeSQL + " AND " + upCode + " = \'" + parentId + "\'";
        } else if(StringUtils.isNotBlank(param)) {
            paramLen = param.split(",").length;
            rowFlag = true;
            treeSQL = treeSQL + " AND " + code + " IN (\'" + param + "\')";
        } else if(StringUtils.isNotBlank(parentParam)) {
            treeSQL = treeSQL + " AND " + upCode + " IN (\'" + parentParam + "\')";
        } else {
            String rootCode = treeDim.getRootCode();
            if(StringUtils.isNotBlank(rootCode)) {
                //treeSQL = treeSQL + " WHERE " + upCode + " = \'" +  rootCode + "\'";
                rowFlag = true;
                paramLen = 1;
                treeSQL = treeSQL + " AND " + code + " = \'" +  rootCode + "\'";
            }
        }

        if(StringUtils.isNotBlank(searchName)) {
            treeSQL = treeSQL + " AND " + name + " LIKE \'%" + searchName + "%\'";
        }

        // 如果配置排序字段，则拼接排序字段
        if(StringUtils.isNotBlank(order)) {
            treeSQL = treeSQL + " ORDER BY " + order;
        }

        return treeSQL;
    }

    /**
     * 拼接查询树数据SQL(多Tab树形控件使用)
     * @param treeDim 树数据维度表
     * @param defalutValue 默认选中的数据
     * @return
     */
    public String getTreeSql(TreeDim treeDim, String defalutValue) {
        String tableName = treeDim.getTable();  // 表名
        String code = treeDim.getCode();  // 编码字段
        String name = treeDim.getName();  // 名称字段
        String upCode = treeDim.getUpcode();  // 上级编码字段

        // 拼接查询查询SQL
        String treeSQL = "SELECT " + code + " AS \"code\"," + name + " AS \"name\"," + upCode + " AS \"upcode\" FROM " + tableName;
        if(StringUtils.isNotBlank(defalutValue)) {
            if(defalutValue.contains(",")) {
                String queryParam = "";
                String[] params = defalutValue.split(",");
                for(int i=0;i<params.length; i++) {
                    queryParam = queryParam + "\'" + params[i] + "\',";
                }
                if(queryParam.endsWith(",")) {
                    queryParam = queryParam.substring(0, queryParam.length() - 1);
                }
                treeSQL = treeSQL + " WHERE " + code + " IN (" + queryParam + ")";
            } else {
                treeSQL = treeSQL + " WHERE " + code + " IN (\'" + defalutValue + "\')";
            }
        }

        return treeSQL;
    }
}
